package com.elephant.core.utils.excel;

import com.elephant.core.utils.sqlit.SqlitUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;

/**
 * getFirstRowNum()和getFirstCellNum()获取的初始行号和列号是物理值，默认从0开始，与表格中看到的相差1
 * @author hai
 * @description
 * @date 14:22 2018/9/29
 */
@Service
public class ReadExcelForxlsx {

    private static final Logger logger = LoggerFactory.getLogger(ReadExcelForxlsx.class);

    public void handleExcel(){

        //excel文件路径
//        String excelPath = "/usr/local/8066/excel/dictionary.db";
        String excelPath = "D:\\xiaozhan\\文档\\18928.xlsx";

        try {
            Connection connection = SqlitUtil.createConnection();
            File excel = new File(excelPath);
            //判断文件是否存在
            if (excel.isFile() && excel.exists()) {

                //.是特殊字符，需要转义！！！！！
                String[] split = excel.getName().split("\\.");
                Workbook wb;
                //根据文件后缀（xls/xlsx）进行判断
                if ( "xls".equals(split[1])){
                    //文件流对象
                    FileInputStream fis = new FileInputStream(excel);
                    wb = new HSSFWorkbook(fis);
                }else if ("xlsx".equals(split[1])){
                    wb = new XSSFWorkbook(excel);
                }else {
                    System.out.println("文件类型错误!");
                    return;
                }

                //开始解析,读取sheet 0
                Sheet sheet = wb.getSheetAt(0);

                //第一行是列名，所以不读
                int firstRowIndex = sheet.getFirstRowNum()+2;
                int lastRowIndex = sheet.getLastRowNum();
                System.out.println("firstRowIndex: "+firstRowIndex);
                System.out.println("lastRowIndex: "+lastRowIndex);

                //遍历行
                for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {

                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();

                        //遍历列
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                            Cell cell = row.getCell(cIndex);
                            if (cell != null && !cell.toString().trim().equals("")) {
//                                String sql = "select * from t_dictionary where trans like '%" + cell.toString() + "%'";
                                String sql = "select * from t_dictionary where trans like '%" + cell.toString() + "%' or examples like '%" + cell.toString() + "%'";
                                ResultSet rs = SqlitUtil.func1(sql, connection);
                                while (rs.next()) {
                                    String trans = rs.getString("trans");
                                    String id = rs.getString("id");
                                    String examples = rs.getString("examples");
                                    String word = rs.getString("word");
                                    logger.info(id + "❤" + word + "❤" + cell.toString() + "❤" + trans + "❤" + examples);
                                }

                                /*String sql = "select * from t_sentence_temp_1 where content like '%" + cell.toString() + "%'";
                                ResultSet rs = SqlitUtil.func1(sql, connection);
                                while (rs.next()) {
                                    String sen_id = rs.getString("sen_id");
                                    String content = rs.getString("content");
                                    logger.info(sen_id + "❤" + content + "❤" + cell.toString());
                                }*/
                            }
                        }
                    }
                }
            } else {
                System.out.println("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {

    }
}
